{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 导入原始数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "orders_train = pd.read_table('./data/orders_train.txt',sep='\\t')\n",
    "users = pd.read_table('./data/users.txt',sep='\\t')\n",
    "spus = pd.read_table('./data/spus.txt',sep='\\t')\n",
    "pois = pd.read_table('./data/pois.txt',sep='\\t')\n",
    "orders_spu_train = pd.read_table('./data/orders_spu_train.txt',sep='\\t')\n",
    "orders_poi_session = pd.read_table('./data/orders_poi_session.txt',sep='\\t')\n",
    "orders_test_poi = pd.read_table('./data/orders_test_poi.txt',sep='\\t')\n",
    "orders_test_spu = pd.read_table('./data/orders_test_spu.txt',sep='\\t')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user_id</th>\n",
       "      <th>wm_order_id</th>\n",
       "      <th>wm_poi_id</th>\n",
       "      <th>aor_id</th>\n",
       "      <th>order_price_interval</th>\n",
       "      <th>order_timestamp</th>\n",
       "      <th>ord_period_name</th>\n",
       "      <th>order_scene_name</th>\n",
       "      <th>aoi_id</th>\n",
       "      <th>takedlvr_aoi_type_name</th>\n",
       "      <th>dt</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>178557</td>\n",
       "      <td>0</td>\n",
       "      <td>2334</td>\n",
       "      <td>6</td>\n",
       "      <td>&lt;29</td>\n",
       "      <td>1623061539</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>未知</td>\n",
       "      <td>20210607</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>175118</td>\n",
       "      <td>1</td>\n",
       "      <td>3315</td>\n",
       "      <td>0</td>\n",
       "      <td>&lt;29</td>\n",
       "      <td>1623032193</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>20210607</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>36208</td>\n",
       "      <td>2</td>\n",
       "      <td>2168</td>\n",
       "      <td>0</td>\n",
       "      <td>&lt;29</td>\n",
       "      <td>1623036350</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>20210607</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>102798</td>\n",
       "      <td>3</td>\n",
       "      <td>3071</td>\n",
       "      <td>0</td>\n",
       "      <td>[29,36)</td>\n",
       "      <td>1623071723</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0</td>\n",
       "      <td>20210607</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>73712</td>\n",
       "      <td>4</td>\n",
       "      <td>2902</td>\n",
       "      <td>0</td>\n",
       "      <td>[49,65)</td>\n",
       "      <td>1623020472</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1</td>\n",
       "      <td>20210607</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   user_id  wm_order_id  wm_poi_id  aor_id order_price_interval  \\\n",
       "0   178557            0       2334       6                  <29   \n",
       "1   175118            1       3315       0                  <29   \n",
       "2    36208            2       2168       0                  <29   \n",
       "3   102798            3       3071       0              [29,36)   \n",
       "4    73712            4       2902       0              [49,65)   \n",
       "\n",
       "   order_timestamp  ord_period_name order_scene_name  aoi_id  \\\n",
       "0       1623061539                3                0     NaN   \n",
       "1       1623032193                1                1     0.0   \n",
       "2       1623036350                1                0     1.0   \n",
       "3       1623071723                4                0     2.0   \n",
       "4       1623020472                0                2     3.0   \n",
       "\n",
       "  takedlvr_aoi_type_name        dt  \n",
       "0                     未知  20210607  \n",
       "1                      0  20210607  \n",
       "2                      0  20210607  \n",
       "3                      0  20210607  \n",
       "4                      1  20210607  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "orders_train.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 1071873 entries, 0 to 1071872\n",
      "Data columns (total 11 columns):\n",
      "user_id                   1071873 non-null int64\n",
      "wm_order_id               1071873 non-null int64\n",
      "wm_poi_id                 1071873 non-null int64\n",
      "aor_id                    1071873 non-null int64\n",
      "order_price_interval      1071873 non-null object\n",
      "order_timestamp           1071873 non-null int64\n",
      "ord_period_name           1071873 non-null int64\n",
      "order_scene_name          1071873 non-null object\n",
      "aoi_id                    1017718 non-null float64\n",
      "takedlvr_aoi_type_name    1071873 non-null object\n",
      "dt                        1071873 non-null int64\n",
      "dtypes: float64(1), int64(7), object(3)\n",
      "memory usage: 90.0+ MB\n"
     ]
    }
   ],
   "source": [
    "orders_train.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>wm_order_id</th>\n",
       "      <th>wm_food_spu_id</th>\n",
       "      <th>dt</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10543</td>\n",
       "      <td>159611</td>\n",
       "      <td>20210607</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>34244</td>\n",
       "      <td>148124</td>\n",
       "      <td>20210607</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>36798</td>\n",
       "      <td>12639</td>\n",
       "      <td>20210607</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3961</td>\n",
       "      <td>137283</td>\n",
       "      <td>20210607</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>39119</td>\n",
       "      <td>109953</td>\n",
       "      <td>20210607</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   wm_order_id  wm_food_spu_id        dt\n",
       "0        10543          159611  20210607\n",
       "1        34244          148124  20210607\n",
       "2        36798           12639  20210607\n",
       "3         3961          137283  20210607\n",
       "4        39119          109953  20210607"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "orders_spu_train.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 3381805 entries, 0 to 3381804\n",
      "Data columns (total 3 columns):\n",
      "wm_order_id       int64\n",
      "wm_food_spu_id    int64\n",
      "dt                int64\n",
      "dtypes: int64(3)\n",
      "memory usage: 77.4 MB\n"
     ]
    }
   ],
   "source": [
    "orders_spu_train.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 通过外连接联结两个表\n",
    "[ref](https://www.cnblogs.com/keye/p/10791705.html)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "three_week_data = pd.merge(orders_train,orders_spu_train,how='outer',on='wm_order_id')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* 合并后会有一个问题，就是wm_food_spu_id，dt_y变成float类型了\n",
    "* 参考这个[blog](https://zhuanlan.zhihu.com/p/35287822)\n",
    "* 这里出现了一个问题，就是之前分析了，有的订单是没有菜品的，这个时候wm_food_spu_id就是NA了，类型转换就会出错。\n",
    "* 我先查看一下这种订单。通过下面的输出可以看到确实有31个订单是没有对应菜品的，这种就作为脏数据，直接先清理掉。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "user_id                   10\n",
       "wm_order_id               10\n",
       "wm_poi_id                 10\n",
       "aor_id                    10\n",
       "order_price_interval      10\n",
       "order_timestamp           10\n",
       "ord_period_name           10\n",
       "order_scene_name          10\n",
       "aoi_id                    10\n",
       "takedlvr_aoi_type_name    10\n",
       "dt_x                      10\n",
       "wm_food_spu_id             0\n",
       "dt_y                       0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "three_week_data[pd.isna(three_week_data['wm_food_spu_id'])].count()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* 按条件删除行（删除wm_food_spu_id为空的行） [参考blog](https://www.jianshu.com/p/21f96fda5b7f)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "three_week_data = three_week_data.drop(three_week_data[pd.isna(three_week_data['wm_food_spu_id'])].index)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* 转换类型"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "three_week_data['wm_food_spu_id'] = three_week_data['wm_food_spu_id'].astype(np.int64)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "three_week_data['dt_y'] = three_week_data['dt_y'].astype(np.int64)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* 检查dt_x与dt_y是否完全相同。从下面的输出为空，可以断定，dt_x与dt_y是完全相同，使用一个就可以了。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user_id</th>\n",
       "      <th>wm_order_id</th>\n",
       "      <th>wm_poi_id</th>\n",
       "      <th>aor_id</th>\n",
       "      <th>order_price_interval</th>\n",
       "      <th>order_timestamp</th>\n",
       "      <th>ord_period_name</th>\n",
       "      <th>order_scene_name</th>\n",
       "      <th>aoi_id</th>\n",
       "      <th>takedlvr_aoi_type_name</th>\n",
       "      <th>dt_x</th>\n",
       "      <th>wm_food_spu_id</th>\n",
       "      <th>dt_y</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [user_id, wm_order_id, wm_poi_id, aor_id, order_price_interval, order_timestamp, ord_period_name, order_scene_name, aoi_id, takedlvr_aoi_type_name, dt_x, wm_food_spu_id, dt_y]\n",
       "Index: []"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "three_week_data[three_week_data['dt_x'] != three_week_data['dt_y']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* 删除dt_y列，重命名dt_x列为dt，并保存这个df为文件"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "three_week_data = three_week_data.drop(['dt_y'],axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "three_week_data = three_week_data.rename({'dt_x':'dt'},axis='columns')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "three_week_data.to_csv('three_week_data.txt',sep='\\t',index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 3381805 entries, 0 to 3381814\n",
      "Data columns (total 12 columns):\n",
      "user_id                   int64\n",
      "wm_order_id               int64\n",
      "wm_poi_id                 int64\n",
      "aor_id                    int64\n",
      "order_price_interval      object\n",
      "order_timestamp           int64\n",
      "ord_period_name           int64\n",
      "order_scene_name          object\n",
      "aoi_id                    float64\n",
      "takedlvr_aoi_type_name    object\n",
      "dt                        int64\n",
      "wm_food_spu_id            int64\n",
      "dtypes: float64(1), int64(8), object(3)\n",
      "memory usage: 335.4+ MB\n"
     ]
    }
   ],
   "source": [
    "three_week_data.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**通过上面的代码生成了前三周的的用户购买菜品的数据，这个数据用来后面生成训练集。**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
